import {Provide, Inject, Init} from '@midwayjs/core';
import {DatabaseService} from './database.service';
import {promisify} from 'util';
import {IKnowledge, IKnowledgeTag, IKnowledgeOptions, IKnowledgeWithTags} from '../interface/knowledge.interface';
import * as sqlite3 from 'sqlite3';

/**
 * 知识库服务类
 * 负责知识库和知识库标签的数据库操作
 */
@Provide()
export class KnowledgeService {
  @Inject()
  databaseService: DatabaseService;

  private db: sqlite3.Database;

  /**
   * 初始化数据库表结构
   */
  @Init()
  async init() {
    this.db = this.databaseService.getDatabase();
    await this.createTables();
  }

  /**
   * 创建知识库相关表
   */
  private async createTables(): Promise<void> {
    const runAsync = promisify(this.db.run.bind(this.db));

    // 创建知识库表
    const createKnowledgeTableSQL = `
      CREATE TABLE IF NOT EXISTS tb_knowledge
      (
        id
        INTEGER
        PRIMARY
        KEY
        AUTOINCREMENT,
        name
        TEXT
        NOT
        NULL,
        content
        TEXT
        NOT
        NULL,
        excalidraw
        TEXT,
        created_at
        DATETIME
        DEFAULT
        CURRENT_TIMESTAMP,
        updated_at
        DATETIME
        DEFAULT
        CURRENT_TIMESTAMP
      )
    `;

    // 创建知识库标签表
    const createKnowledgeTagTableSQL = `
      CREATE TABLE IF NOT EXISTS tb_knowledge_tag
      (
        id
        INTEGER
        PRIMARY
        KEY
        AUTOINCREMENT,
        knowledge_id
        INTEGER
        NOT
        NULL,
        name
        TEXT
        NOT
        NULL,
        created_at
        DATETIME
        DEFAULT
        CURRENT_TIMESTAMP,
        FOREIGN
        KEY
      (
        knowledge_id
      ) REFERENCES tb_knowledge
      (
        id
      ) ON DELETE CASCADE
        )
    `;

    await runAsync(createKnowledgeTableSQL);
    await runAsync(createKnowledgeTagTableSQL);
  }

  /**
   * 获取知识库列表
   * @returns 知识库列表（包含标签）
   */
  async getKnowledgeList(): Promise<IKnowledgeWithTags[]> {
    const allAsync = promisify(this.db.all.bind(this.db));

    // 查询所有知识库
    const knowledgeList = await allAsync(
      'SELECT * FROM tb_knowledge ORDER BY id DESC'
    ) as IKnowledge[];

    // 为每个知识库查询对应的标签
    const result: IKnowledgeWithTags[] = [];

    for (const knowledge of knowledgeList) {
      const tags = await this.getKnowledgeTags(knowledge.id);
      result.push({
        ...knowledge,
        tags
      });
    }

    return result;
  }

  /**
   * 根据条件查询知识库
   * @param options 查询条件
   * @returns 知识库列表（包含标签）
   */
  async getKnowledgeByOptions(options: IKnowledgeOptions): Promise<IKnowledgeWithTags[]> {
    const allAsync = promisify(this.db.all.bind(this.db));
    let sql = 'SELECT * FROM tb_knowledge WHERE 1=1';
    const params = [];

    if (options.id) {
      sql += ' AND id = ?';
      params.push(options.id);
    }

    if (options.name) {
      sql += ' AND name LIKE ?';
      params.push(`%${options.name}%`);
    }

    sql += ' ORDER BY id DESC';

    const knowledgeList = await allAsync(sql, params) as IKnowledge[];

    // 为每个知识库查询对应的标签
    const result: IKnowledgeWithTags[] = [];

    for (const knowledge of knowledgeList) {
      const tags = await this.getKnowledgeTags(knowledge.id);
      result.push({
        ...knowledge,
        tags
      });
    }

    return result;
  }

  /**
   * 根据ID获取单个知识库
   * @param id 知识库ID
   * @returns 知识库信息（包含标签）
   */
  async getKnowledgeById(id: number): Promise<IKnowledgeWithTags | null> {
    const getAsync = promisify(this.db.get.bind(this.db));

    const knowledge = await getAsync(
      'SELECT * FROM tb_knowledge WHERE id = ?',
      [id]
    ) as IKnowledge | undefined;

    if (!knowledge) {
      return null;
    }

    const tags = await this.getKnowledgeTags(knowledge.id);

    return {
      ...knowledge,
      tags
    };
  }

  /**
   * 创建知识库
   * @param knowledge 知识库信息
   * @param tags 标签列表
   * @returns 创建的知识库ID
   */
  async createKnowledge(knowledge: IKnowledge, tags: IKnowledgeTag[] = []): Promise<number> {
    const runAsync = promisify(this.db.run.bind(this.db));

    // 开始事务
    await runAsync('BEGIN TRANSACTION');

    try {
      // 插入知识库
      const _stmt = await runAsync(
        'INSERT INTO tb_knowledge (name, content, excalidraw) VALUES (?, ?, ?)',
        [knowledge.name, knowledge.content, knowledge.excalidraw || null]
      );

      // 获取最后插入的ID
      const getLastIdAsync = promisify(this.db.get.bind(this.db));
      const result = await getLastIdAsync('SELECT last_insert_rowid() as lastID') as { lastID: number };

      const knowledgeId = result.lastID;

      // 插入标签
      if (tags && tags.length > 0) {
        for (const tagItem of tags) {
          await runAsync(
            'INSERT INTO tb_knowledge_tag (knowledge_id, name) VALUES (?, ?)',
            [knowledgeId, tagItem?.name]
          );
        }
      }

      // 提交事务
      await runAsync('COMMIT');

      return knowledgeId;
    } catch (error) {
      // 回滚事务
      await runAsync('ROLLBACK');
      throw error;
    }
  }

  /**
   * 搜索相关笔记
   * @param query 查询关键词
   * @returns 相关笔记列表
   */
  async searchRelatedNotes(query: string): Promise<IKnowledge[]> {
    const allAsync = promisify(this.db.all.bind(this.db));

    if (!query || !query.trim()) {
      return [];
    }

    const searchTerm = `%${query.trim()}%`;

    // 搜索标题和内容中包含关键词的知识库
    const sql = `
      SELECT id,name, content
      FROM tb_knowledge
      WHERE name LIKE ?
         OR content LIKE ?
      ORDER BY CASE
                 WHEN name LIKE ? THEN 1
                 WHEN content LIKE ? THEN 2
                 ELSE 3
                 END,
               id DESC LIMIT 10
    `;

    const results = await allAsync(sql, [searchTerm, searchTerm, searchTerm, searchTerm]) as Array<{
      id: any;
      name: string,
      content: string,
      shortContent:string
    }>;

    // 格式化返回结果
    return results.map(item => {
      // 如果标题匹配，优先显示标题
      if (item.name.toLowerCase().includes(query.toLowerCase())) {
        return {
          id: item?.id,
          name: item?.name,
          content: item?.content
        }
      }
      // 如果内容匹配，显示内容片段
      const contentLower = item.content.toLowerCase();
      const queryLower = query.toLowerCase();
      const index = contentLower.indexOf(queryLower);

      if (index !== -1) {
        // 提取关键词前后的文本片段
        const start = Math.max(0, index - 20);
        const end = Math.min(item.content.length, index + query.length + 20);
        const snippet = item.content.substring(start, end);
        return {
          id: item?.id,
          name: item?.name,
          shortContent: snippet,
          content: item?.content
        }
      }

      return {
        id: item?.id,
        name: item?.name,
        content: item?.content
      }
    });
  }

  /**
   * 更新知识库
   * @param knowledge 知识库信息
   * @param tags 标签列表（如果提供，将替换原有标签）
   * @returns 是否更新成功
   */
  async updateKnowledge(knowledge: IKnowledge, tags?: IKnowledgeTag[]): Promise<boolean> {
    const runAsync = promisify(this.db.run.bind(this.db));
    const getAsync = promisify(this.db.get.bind(this.db));

    // 检查知识库是否存在
    const existingKnowledge = await getAsync(
      'SELECT id FROM tb_knowledge WHERE id = ?',
      [knowledge.id]
    );

    if (!existingKnowledge) {
      return false;
    }

    // 开始事务
    await runAsync('BEGIN TRANSACTION');

    try {
      // 更新知识库
      let updateFields = [];
      let updateParams = [];

      if (knowledge.name !== undefined) {
        updateFields.push('name = ?');
        updateParams.push(knowledge.name);
      }

      if (knowledge.content !== undefined) {
        updateFields.push('content = ?');
        updateParams.push(knowledge.content);
      }

      if (knowledge.excalidraw !== undefined) {
        updateFields.push('excalidraw = ?');
        updateParams.push(knowledge.excalidraw);
      }

      if (updateFields.length > 0) {
        updateFields.push('updated_at = CURRENT_TIMESTAMP');

        await runAsync(
          `UPDATE tb_knowledge
           SET ${updateFields.join(', ')}
           WHERE id = ?`,
          [...updateParams, knowledge.id]
        );
      }

      // 如果提供了标签，则更新标签
      if (tags !== undefined) {
        // 删除原有标签
        await runAsync(
          'DELETE FROM tb_knowledge_tag WHERE knowledge_id = ?',
          [knowledge.id]
        );

        // 插入新标签
        if (tags && tags.length > 0) {
          for (const tagItem of tags) {
            await runAsync(
              'INSERT INTO tb_knowledge_tag (knowledge_id, name) VALUES (?, ?)',
              [knowledge.id, tagItem?.name]
            );
          }
        }
      }

      // 提交事务
      await runAsync('COMMIT');

      return true;
    } catch (error) {
      // 回滚事务
      await runAsync('ROLLBACK');
      throw error;
    }
  }

  /**
   * 删除知识库
   * @param id 知识库ID
   * @returns 是否删除成功
   */
  async deleteKnowledge(id: number): Promise<boolean> {
    const runAsync = promisify(this.db.run.bind(this.db));
    const getAsync = promisify(this.db.get.bind(this.db));

    // 检查知识库是否存在
    const existingKnowledge = await getAsync(
      'SELECT id FROM tb_knowledge WHERE id = ?',
      [id]
    );

    if (!existingKnowledge) {
      return false;
    }

    // 开始事务
    await runAsync('BEGIN TRANSACTION');

    try {
      // 删除知识库标签
      await runAsync(
        'DELETE FROM tb_knowledge_tag WHERE knowledge_id = ?',
        [id]
      );

      // 删除知识库
      await runAsync(
        'DELETE FROM tb_knowledge WHERE id = ?',
        [id]
      );

      // 提交事务
      await runAsync('COMMIT');

      return true;
    } catch (error) {
      // 回滚事务
      await runAsync('ROLLBACK');
      throw error;
    }
  }

  /**
   * 获取知识库的标签
   * @param knowledgeId 知识库ID
   * @returns 标签列表
   */
  async getKnowledgeTags(knowledgeId: number): Promise<IKnowledgeTag[]> {
    const allAsync = promisify(this.db.all.bind(this.db));

    return await allAsync(
      'SELECT * FROM tb_knowledge_tag WHERE knowledge_id = ?',
      [knowledgeId]
    ) as IKnowledgeTag[];
  }

  /**
   * 删除知识库标签
   * @param tagId 标签ID
   * @returns 是否删除成功
   */
  async deleteKnowledgeTag(tagId: number): Promise<boolean> {
    const runAsync = promisify(this.db.run.bind(this.db));
    const getAsync = promisify(this.db.get.bind(this.db));

    // 检查标签是否存在
    const existingTag = await getAsync(
      'SELECT id FROM tb_knowledge_tag WHERE id = ?',
      [tagId]
    );

    if (!existingTag) {
      return false;
    }

    try {
      // 删除标签
      await runAsync(
        'DELETE FROM tb_knowledge_tag WHERE id = ?',
        [tagId]
      );

      return true;
    } catch (error) {
      throw error;
    }
  }
}
